#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Datetime: 2021/12/05 13:14
# @Author  : CHEN Wang
# @Site    :
# @File    : binance.py
# @Software: PyCharm

"""
脚本说明: 从binance api上获取相关信息

币安api文档:
https://github.com/binance
https://binance-docs.github.io/apidocs/#change-log
https://github.com/binance-exchange/binance-official-api-docs/blob/master/rest-api.md
"""

import os
import requests
import json
import datetime
import time
import pandas as pd
from retrying import retry
from urllib.parse import urljoin
from dateutil.tz import *
from quant_researcher.quant.project_tool.localize import DATA_DIR
from joblib import Parallel, delayed
from quant_researcher.quant.project_tool.time_tool import datetime_to_timestamp, timestamp_to_str, timestamp_to_datetime
from quant_researcher.quant.project_tool.logger.my_logger import LOG
from quant_researcher.quant.project_tool.time_tool import get_today
from requests.exceptions import ConnectTimeout, SSLError, ReadTimeout, ConnectionError

TIMEOUT = 10
ILOVECHINA = "同学！！你知道什么叫做科学上网么？如果你不知道的话，蓝灯，喵帕斯，VPS，阴阳师，v2ray，随便来一个！"
Binance_spot_base_url = "https://api.binance.com"
Binance_um_futures_base_url = 'https://fapi.binance.com'
Binance_cm_futures_base_url = 'https://dapi.binance.com'
Binance_options_base_url = 'https://vapi.binance.com'

"""
QUANTAXIS 和 binance 的 frequency 常量映射关系
"""
Binance2FREQUENCY_DICT = {
    "1m": '1min',
    "5m": '5min',
    "15m": '15min',
    "30m": '30min',
    "1h": '60min',
    "8h": '8hour',
    "1d": 'day',
}
"""
binance 只允许一次获取 500bar，时间请求超过范围则只返回最新500条
"""
FREQUENCY_SHIFTING = {
    "1m": 30000,
    "5m": 150000,
    "15m": 450000,
    "30m": 900000,
    "1h": 1800000,
    "8h": 1800000,
    "1d": 14400000,
}


@retry(stop_max_attempt_number=3, wait_random_min=50, wait_random_max=100)
def fetch_binance_exchangeinfo(type='spot', trading=True):
    """
    Current exchange trading rules and symbol information

    :param str, type: 支持‘spot’, 'um_futures', 'cm_futures', 'options'
    :param bool, trading: 是否只保留正在交易的交易对
    :return:
    """
    if type == 'spot':
        url = urljoin(Binance_spot_base_url, "/api/v3/exchangeInfo")
    elif type == 'um_futures':
        url = urljoin(Binance_um_futures_base_url, "/fapi/v1/exchangeInfo")
    elif type == 'cm_futures':
        url = urljoin(Binance_cm_futures_base_url, "/dapi/v1/exchangeInfo")
    elif type == 'options':
        url = urljoin(Binance_options_base_url, "/eapi/v1/exchangeInfo")
    else:
        raise NotImplementedError

    retries = 1
    datas = list()
    while (retries != 0) and (retries <= 10):
        try:
            req = requests.get(url, timeout=TIMEOUT)
            retries = 0
        except (ConnectTimeout, ConnectionError, SSLError, ReadTimeout):
            retries = retries + 1
            if (retries % 5 == 0):
                print(ILOVECHINA)
            print(f"Retry {url} #{retries - 1}")
            time.sleep(0.5)

        if (retries == 0):
            # 成功获取才处理数据，否则继续尝试连接
            all_data = json.loads(req.content)
            if type == 'options':
                symbol_lists = all_data['data']['optionSymbols']
            else:
                symbol_lists = all_data["symbols"]
            if len(symbol_lists) == 0:
                return pd.DataFrame([])
            for symbol in symbol_lists:
                if trading and type in ['spot', 'um_futures']:
                    # 只导入上架交易对
                    if (symbol['status'] == 'TRADING'):
                        datas.append(symbol)
                elif trading and type == 'cm_futures':
                    if (symbol['contractStatus'] == 'TRADING'):
                        datas.append(symbol)
                else:
                    datas.append(symbol)
    if datas:
        res = pd.DataFrame(datas)
        return res
    else:
        return None


@retry(stop_max_attempt_number=3, wait_random_min=50, wait_random_max=100)
def fetch_binance_data_with_auto_retry(url, params):
    """
    Get the symbol‘s data
    """
    proxy = {'http': '127.0.0.1:41092'}
    retries = 1
    while (retries != 0) and (retries <= 10):
        try:
            req = requests.get(url=url, params=params, timeout=TIMEOUT, proxies=proxy)
            # 防止频率过快被断连
            time.sleep(0.5)
            retries = 0
        except (ConnectTimeout, ConnectionError, SSLError, ReadTimeout):
            retries = retries + 1
            if (retries % 5 == 0):
                print(ILOVECHINA)
            print(f"Retry {url} #{retries - 1}")
            time.sleep(0.5)

        if (retries == 0):
            # 成功获取才处理数据，否则继续尝试连接
            datas = json.loads(req.content)
            if len(datas) == 0:
                print('该段区间数据读取为空')
                return None
            return datas

    return None


def fetch_binance_kline(symbol, start_time, end_time, frequency, callback_func=None):
    """
    Get the latest symbol‘s candlestick data
    时间倒序切片获取算法，是各大交易所获取1min数据的神器，因为大部分交易所直接请求跨月跨年的1min分钟数据
    会直接返回空值，只有将 start_epoch，end_epoch 切片细分到 200/300 bar 以内，才能正确返回 kline，
    火币和binance，OKEx 均为如此，直接用跨年时间去直接请求上万bar 的 kline 数据永远只返回最近200条数据。
    """

    def format_binance_klines_data_fields(datas, symbol, frequency):
        """
        # 归一化数据字段，转换填充必须字段，删除多余字段
        参数名 	类型 	描述
        time 	String 	开始时间
        open 	String 	开盘价格
        high 	String 	最高价格
        low 	String 	最低价格
        close 	String 	收盘价格
        volume 	String 	交易量
        """

        column_names = ['start_time', 'open', 'high', 'low', 'close', 'volume', 'close_time', 'quote_asset_volume',
                        'num_trades', 'buy_base_asset_volume', 'buy_quote_asset_volume', 'Ignore']

        frame = pd.DataFrame(datas, columns=column_names)
        frame = frame.astype(float)
        frame['symbol'] = 'BINANCE.{}'.format(symbol)
        # UTC时间戳转换为UTC日期时间
        frame['start_time'] = frame.apply(lambda x: int(x['start_time'] / 1000), axis=1)
        frame['datetime'] = frame['start_time'].apply(timestamp_to_datetime, tz_str='+0000')
        frame['date'] = frame['datetime'].dt.strftime('%Y-%m-%d')
        frame['datetime'] = frame['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        frame.rename({'num_trades': 'trade', 'start_time': 'time_stamp', 'buy_quote_asset_volume': 'amount'}, axis=1, inplace=True)
        if (frequency not in ['1day', Binance2FREQUENCY_DICT['1d'], '1d']):
            frame['type'] = Binance2FREQUENCY_DICT[frequency]
        frame.drop(['close_time', 'quote_asset_volume', 'buy_base_asset_volume', 'Ignore'], axis=1, inplace=True)
        return frame

    datas = list()
    reqParams = {}
    reqParams['from'] = end_time - FREQUENCY_SHIFTING[frequency]
    if reqParams['from'] < start_time:
        reqParams['from'] = start_time
    reqParams['to'] = end_time

    while (reqParams['to'] > start_time):
        if ((reqParams['from'] > datetime_to_timestamp())) or ((reqParams['from'] > reqParams['to'])):
            # 出现“未来”时间，一般是默认时区设置，或者时间窗口滚动前移错误造成的
            LOG.info(
                'A unexpected \'Future\' timestamp got, Please check self.missing_data_list_func param \'tzlocalize\' '
                'set. More info: {:s}@{:s} at {:s} but current time is {}'
                    .format(
                    symbol,
                    frequency,
                    timestamp_to_str(reqParams['from']),
                    timestamp_to_str(datetime_to_timestamp())
                )
            )
            # 跳到下一个时间段
            reqParams['to'] = int(reqParams['from'] - 1)
            reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
            if reqParams['from'] < start_time:
                reqParams['from'] = start_time
            continue

        url = urljoin(Binance_spot_base_url, "/api/v1/klines")
        params = {
            "symbol": symbol,
            "interval": frequency,
            "startTime": int(reqParams['from'] * 1000),
            "endTime": int(reqParams['to'] * 1000)
        }
        print(f"开始获取{timestamp_to_str(reqParams['from'])} - {timestamp_to_str(reqParams['to'])}的数据")
        klines = fetch_binance_data_with_auto_retry(url, params)
        if (klines is None) or (len(klines) == 0) or ('error' in klines):
            # 出错放弃
            break

        reqParams['to'] = int(reqParams['from'] - 1)
        reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
        if reqParams['from'] < start_time:
            reqParams['from'] = start_time

        if (klines is None) or ((len(datas) > 0) and (klines[-1][0] == datas[-1][0])):
            # 没有更多数据
            break

        datas.extend(klines)

        if (callback_func is not None):
            frame = format_binance_klines_data_fields(klines, symbol, frequency)
            callback_func(frame, Binance2FREQUENCY_DICT[frequency])

    if len(datas) == 0:
        return None

    # 归一化数据字段，转换填充必须字段，删除多余字段
    frame = format_binance_klines_data_fields(datas, symbol, frequency)
    frame.set_index('date', inplace=True)
    frame.sort_index(inplace=True)

    return frame


def fetch_binance_funding_rate(symbol, market_type, start_time, end_time, frequency, callback_func=None):
    """
    Get the latest symbol‘s funding_rate data
    时间倒序切片获取算法，是各大交易所获取1min数据的神器，因为大部分交易所直接请求跨月跨年的1min分钟数据
    会直接返回空值，只有将 start_epoch，end_epoch 切片细分到 200/300 bar 以内，才能正确返回 kline，
    火币和binance，OKEx 均为如此，直接用跨年时间去直接请求上万bar 的 kline 数据永远只返回最近200条数据。
    """

    def format_funding_rate_data(datas, symbol, frequency):
        """
        # 归一化数据字段，转换填充必须字段，删除多余字段
        参数名 	类型 	描述
        time 	String 	开始时间
        open 	String 	开盘价格
        high 	String 	最高价格
        low 	String 	最低价格
        close 	String 	收盘价格
        volume 	String 	交易量
        """

        return frame

    datas = list()
    reqParams = {}
    reqParams['from'] = end_time - FREQUENCY_SHIFTING[frequency]
    if reqParams['from'] < start_time:
        reqParams['from'] = start_time
    reqParams['to'] = end_time

    while (reqParams['to'] > start_time):
        if ((reqParams['from'] > datetime_to_timestamp())) or ((reqParams['from'] > reqParams['to'])):
            # 出现“未来”时间，一般是默认时区设置，或者时间窗口滚动前移错误造成的
            LOG.info(
                'A unexpected \'Future\' timestamp got, Please check self.missing_data_list_func param \'tzlocalize\' '
                'set. More info: {:s}@{:s} at {:s} but current time is {}'
                    .format(
                    symbol,
                    frequency,
                    timestamp_to_str(reqParams['from']),
                    timestamp_to_str(datetime_to_timestamp())
                )
            )
            # 跳到下一个时间段
            reqParams['to'] = int(reqParams['from'] - 1)
            reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
            if reqParams['from'] < start_time:
                reqParams['from'] = start_time
            continue
        if market_type == 'um_futures':
            url = urljoin(Binance_um_futures_base_url, "/fapi/v1/fundingRate")
        else:
            url = urljoin(Binance_cm_futures_base_url, "/dapi/v1/fundingRate")
        params = {
            "symbol": symbol,
            "startTime": int(reqParams['from'] * 1000),
            "endTime": int(reqParams['to'] * 1000)
        }
        print(f"开始获取{timestamp_to_str(reqParams['from'])} - {timestamp_to_str(reqParams['to'])}的数据")
        funding_rate = fetch_binance_data_with_auto_retry(url, params)

        reqParams['to'] = int(reqParams['from'] - 1)
        reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
        if reqParams['from'] < start_time:
            reqParams['from'] = start_time

        if (funding_rate is None):
            # 数据获取出错
            break
        if ((len(datas) > 0) and (funding_rate[-1]['fundingTime'] == datas[-1]['fundingTime'])):
            # 没有更多数据
            break

        datas.extend(funding_rate)

        if (callback_func is not None):
            frame = format_funding_rate_data(funding_rate, symbol, frequency)
            callback_func(frame, Binance2FREQUENCY_DICT[frequency])

    if len(datas) == 0:
        return None

    column_names = ['symbol', 'fundingTime', 'fundingRate']
    frame = pd.DataFrame(datas, columns=column_names)
    frame.rename({'fundingTime': 'timestamp', 'fundingRate': 'funding_rate'}, axis=1, inplace=True)
    frame.sort_values(by='timestamp', inplace=True)
    return frame


def fetch_binance_margin_interest_rate(asset, start_time, end_time, callback_func=None):
    """
    https://www.binance.com/en/margin/interest-history

    Get the latest symbol‘s funding_rate data
    时间倒序切片获取算法，是各大交易所获取1min数据的神器，因为大部分交易所直接请求跨月跨年的1min分钟数据
    会直接返回空值，只有将 start_epoch，end_epoch 切片细分到 200/300 bar 以内，才能正确返回 kline，
    火币和binance，OKEx 均为如此，直接用跨年时间去直接请求上万bar 的 kline 数据永远只返回最近200条数据。
    """

    def format_funding_rate_data(datas, symbol, frequency):
        """
        # 归一化数据字段，转换填充必须字段，删除多余字段
        参数名 	类型 	描述
        time 	String 	开始时间
        open 	String 	开盘价格
        high 	String 	最高价格
        low 	String 	最低价格
        close 	String 	收盘价格
        volume 	String 	交易量
        """

        return frame

    shift_gap = 25 * 24 * 60 * 60

    datas = list()
    reqParams = {}
    reqParams['from'] = end_time - shift_gap
    if reqParams['from'] < start_time:
        reqParams['from'] = start_time
    reqParams['to'] = end_time

    while (reqParams['to'] > start_time):
        if ((reqParams['from'] > datetime_to_timestamp())) or ((reqParams['from'] > reqParams['to'])):
            # 出现“未来”时间，一般是默认时区设置，或者时间窗口滚动前移错误造成的
            LOG.info(
                'A unexpected \'Future\' timestamp got, Please check self.missing_data_list_func param \'tzlocalize\' set. More info: {:s}@{:s} at {:s} but current time is {}'
                    .format(
                    asset,
                    timestamp_to_str(reqParams['from']),
                    timestamp_to_str(datetime_to_timestamp())
                )
            )
            # 跳到下一个时间段
            reqParams['to'] = int(reqParams['from'] - 1)
            reqParams['from'] = int(reqParams['from'] - shift_gap)
            if reqParams['from'] < start_time:
                reqParams['from'] = start_time
            continue

        url = 'https://www.binance.com/bapi/margin/v1/public/margin/vip/spec/history-interest-rate'

        params = {
            "asset": asset,
            "vipLevel": 0,
            "size": 90,
            "startTime": int(reqParams['from'] * 1000),
            "endTime": int(reqParams['to'] * 1000)
        }
        print(f"开始获取{timestamp_to_str(reqParams['from'])} - {timestamp_to_str(reqParams['to'])}的数据")
        margin_interest_rate = fetch_binance_data_with_auto_retry(url, params)

        reqParams['to'] = int(reqParams['from'] - 1)
        reqParams['from'] = int(reqParams['from'] - shift_gap)
        if reqParams['from'] < start_time:
            reqParams['from'] = start_time

        if (margin_interest_rate is None):
            # 数据获取出错
            break
        if ((len(datas) > 0) and (not margin_interest_rate['data'])):
            # 没有更多数据
            break

        datas.extend(margin_interest_rate['data'])

        if (callback_func is not None):
            frame = format_funding_rate_data(margin_interest_rate)

    if len(datas) == 0:
        return None

    frame = pd.DataFrame(datas)
    frame['dailyInterestRate'] = frame['dailyInterestRate'].astype(float)
    frame['dailyInterestRate'] = frame['dailyInterestRate'] * 365  # 年化，并转为%
    frame.rename({'dailyInterestRate': 'margin_interest_rate'}, axis=1, inplace=True)
    frame.sort_values(by='timestamp', inplace=True)
    frame['timestamp'] = frame['timestamp'].astype(float)

    frame.drop_duplicates(subset=['timestamp'], keep='first', inplace=True)

    return frame


def fetch_binance_usdtcny_premium():
    """
    从https://c2c.binance.com/en/trade/sell/USDT?fiat=CNY&payment=ALL 获取USDT场外兑CNY折溢价数据

    :return:
    """

    header = {
        'accept': '*/*',
        'accept-encoding': 'gzip, deflate, br',
        'accept-language': 'en-US,en;q=0.9,zh-CN;q=0.8,zh-TW;q=0.7,zh;q=0.6',
        'bnc-uuid': 'dac63e8f-92fe-431d-b0ae-b10060f6ab18',
        'c2ctype': 'c2c_merchant',
        'clienttype': 'web',
        'content-length': '80',
        'content-type': 'application/json',
        'cookie': 'cid=qbzWrcxF; bnc-uuid=dac63e8f-92fe-431d-b0ae-b10060f6ab18; source=organic; campaign=www.google.com.hk; fiat-prefer-currency=CNY; BNC_FV_KEY=33ec8f7b1389a8c19e7a3840071071611df7c4ec; _gcl_au=1.1.1536750868.1663839849; se_gd=VIMVQB1EIFLB1cUUBGFcgZZEgFRkQBXVVIR5QVEdFJcUAUlNXVEB1; userPreferredCurrency=CNY_USD; lang=en; _gid=GA1.2.273985645.1664329990; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218364954910161f-065d9ef00e9bc7-26021c51-2073600-18364954911169d%22%2C%22first_id%22%3A%22%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E8%87%AA%E7%84%B6%E6%90%9C%E7%B4%A2%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC%22%2C%22%24latest_referrer%22%3A%22https%3A%2F%2Fwww.google.com.hk%2F%22%7D%2C%22identities%22%3A%22eyIkaWRlbnRpdHlfY29va2llX2lkIjoiMTgzNjQ5NTQ5MTAxNjFmLTA2NWQ5ZWYwMGU5YmM3LTI2MDIxYzUxLTIwNzM2MDAtMTgzNjQ5NTQ5MTExNjlkIn0%3D%22%2C%22history_login_id%22%3A%7B%22name%22%3A%22%22%2C%22value%22%3A%22%22%7D%2C%22%24device_id%22%3A%2218364954910161f-065d9ef00e9bc7-26021c51-2073600-18364954911169d%22%7D; BNC_FV_KEY_EXPIRE=1664351608188; OptanonAlertBoxClosed=2022-09-28T01:53:35.020Z; sys_mob=no; videoViewed=yes; common_fiat=CNY; showBlockMarket=true; OptanonConsent=isGpcEnabled=0&datestamp=Wed+Sep+28+2022+11%3A08%3A53+GMT%2B0800+(China+Standard+Time)&version=6.34.0&isIABGlobal=false&hosts=&consentId=d1e70055-0783-4fcf-a835-314e671027b0&interactionCount=2&landingPath=NotLandingPage&groups=C0001%3A1%2CC0003%3A1%2CC0004%3A1%2CC0002%3A1&AwaitingReconsent=false&geolocation=GB%3BENG; _gat_UA-162512367-1=1; _ga=GA1.2.1569260945.1663839845; _ga_3WP50LGEEC=GS1.1.1664329988.3.1.1664334546.48.0.0',
        'csrftoken': 'd41d8cd98f00b204e9800998ecf8427e',
        'device-info': 'eyJzY3JlZW5fcmVzb2x1dGlvbiI6IjE5MjAsMTA4MCIsImF2YWlsYWJsZV9zY3JlZW5fcmVzb2x1dGlvbiI6IjE5MjAsMTAzMiIsInN5c3RlbV92ZXJzaW9uIjoiV2luZG93cyAxMCIsImJyYW5kX21vZGVsIjoidW5rbm93biIsInN5c3RlbV9sYW5nIjoiZW4tVVMiLCJ0aW1lem9uZSI6IkdNVCs4IiwidGltZXpvbmVPZmZzZXQiOi00ODAsInVzZXJfYWdlbnQiOiJNb3ppbGxhLzUuMCAoV2luZG93cyBOVCAxMC4wOyBXaW42NDsgeDY0KSBBcHBsZVdlYktpdC81MzcuMzYgKEtIVE1MLCBsaWtlIEdlY2tvKSBDaHJvbWUvMTA1LjAuMC4wIFNhZmFyaS81MzcuMzYiLCJsaXN0X3BsdWdpbiI6IlBERiBWaWV3ZXIsQ2hyb21lIFBERiBWaWV3ZXIsQ2hyb21pdW0gUERGIFZpZXdlcixNaWNyb3NvZnQgRWRnZSBQREYgVmlld2VyLFdlYktpdCBidWlsdC1pbiBQREYiLCJjYW52YXNfY29kZSI6IjUyODg2OTczIiwid2ViZ2xfdmVuZG9yIjoiR29vZ2xlIEluYy4gKEludGVsKSIsIndlYmdsX3JlbmRlcmVyIjoiQU5HTEUgKEludGVsLCBJbnRlbChSKSBVSEQgR3JhcGhpY3MgNzUwIERpcmVjdDNEMTEgdnNfNV8wIHBzXzVfMCwgRDNEMTEpIiwiYXVkaW8iOiIxMjQuMDQzNDc1Mjc1MTYwNzQiLCJwbGF0Zm9ybSI6IldpbjMyIiwid2ViX3RpbWV6b25lIjoiQXNpYS9TaGFuZ2hhaSIsImRldmljZV9uYW1lIjoiQ2hyb21lIFYxMDUuMC4wLjAgKFdpbmRvd3MpIiwiZmluZ2VycHJpbnQiOiIyZjkzOGQ1NWRlZWI0NzhlMGIxNWNhZmNlYWZlZDE0ZCIsImRldmljZV9pZCI6IiIsInJlbGF0ZWRfZGV2aWNlX2lkcyI6IiJ9',
        'fvideo-id': '33ec8f7b1389a8c19e7a3840071071611df7c4ec',
        'lang': 'en',
        'host': 'c2c.binance.com',
        'origin': 'https://c2c.binance.com',
        'referer': 'https://c2c.binance.com/en/express/buy/USDT/CNY',
        'sec-ch-ua': '"Google Chrome";v="105", "Not)A;Brand";v="8", "Chromium";v="105"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': 'Windows',
        'sec-fetch-dest': 'empty',
        'sec-fetch-mode': 'cors',
        'sec-fetch-site': 'same-origin',
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36',
        'x-trace-id': '12d23ec7-ef92-4ab5-aca5-e56ab247e2e9',
        'x-ui-request-trace': '12d23ec7-ef92-4ab5-aca5-e56ab247e2e9'}

    timestamp_ = int(time.time() * 1000)
    datetime_ = timestamp_to_str(timestamp_ / 1000, fmt='%Y-%m-%d %H:%M:%S', tz_str='+0000')
    date_ = timestamp_to_str(timestamp_ / 1000, fmt='%Y-%m-%d', tz_str='+0000')

    url = f'https://c2c.binance.com/bapi/c2c/v2/public/c2c/adv/quoted-price'
    # orderbook_url = f'https://www.okx.com/v3/c2c/tradingOrders/books?t={timestamp_}&quoteCurrency=cny&baseCurrency=usdt&side=all' \
    #                 f'&paymentMethod=all&userType=all&showTrade=false&receivingAds=false&showFollow=false&showAlreadyTraded=false&isAbleFilter=false'

    post_json = {'fiat': "CNY",
                 'payment': "ALL"}

    res1 = requests.post(url, headers=header, timeout=60)
    df_ticker = pd.DataFrame(json.loads(res1.content)['data'], index=[0])
    df_ticker.rename(columns={'otcTicker': 'usdtcny'}, inplace=True)
    df_ticker['usdtcny'] = df_ticker['usdtcny'].astype(float)
    df_ticker['timestamp'] = timestamp_
    df_ticker['datetime'] = datetime_
    df_ticker['date'] = date_

    return df_ticker


def fetch_binance_perpetual_leverage_margin():
    """
    从https://www.binance.com/en/futures/trading-rules/perpetual/leverage-margin 获取USDT场外兑CNY折溢价数据

    :return:
    """

    cookie_str = 'cid=BL1hM0k8; aliyungf_tc=8e7e606650c0669dbd58a14146bf6b418d37a7cf54060c97c5b427b5c7d25477; _gid=GA1.2.164483161.1682500696; bnc-uuid=91d90c73-9cf1-4ade-be61-a295a20c816a; _gcl_au=1.1.2043059494.1682500697; userPreferredCurrency=USD_USD; sajssdk_2015_cross_new_user=1; sensorsdata2015jssdkcross={"distinct_id":"187bcdb3e42d5e-008d2b3183afef28-26031b51-2073600-187bcdb3e43d97","first_id":"","props":{"$latest_traffic_source_type":"直接流量","$latest_search_keyword":"未取到值_直接打开","$latest_referrer":""},"identities":"eyIkaWRlbnRpdHlfY29va2llX2lkIjoiMTg3YmNkYjNlNDJkNWUtMDA4ZDJiMzE4M2FmZWYyOC0yNjAzMWI1MS0yMDczNjAwLTE4N2JjZGIzZTQzZDk3In0=","history_login_id":{"name":"","value":""},"$device_id":"187bcdb3e42d5e-008d2b3183afef28-26031b51-2073600-187bcdb3e43d97"}; BNC_FV_KEY=332b3ed89496af11af79cae3b3ad780ef0735558; BNC_FV_KEY_EXPIRE=1682522297818; _cq_duid=1.1682500709.qyNsgjxKGlEyhn21; _cq_suid=1.1682500709.hP4TCJmsXtKoJhy9; _ga_3WP50LGEEC=GS1.1.1682500697.1.1.1682500975.60.0.0; _ga=GA1.2.572619913.1682500696; source=referral; campaign=www.binancezh.jp; _uetsid=47ba8d20e41311ed90902b47f592f68c; _uetvid=47babd70e41311ed86f0bb12beed819c; lang=en'
    cookie_str = cookie_str.encode("utf-8").decode("latin1")
    cookies = {cookies.split('=')[0]: cookies.split('=')[-1] for cookies in cookie_str.split('; ')}

    header = {
        'bnc-uuid': '91d90c73-9cf1-4ade-be61-a295a20c816a',
        'content-type': 'application/json',
        'csrftoken': 'd41d8cd98f00b204e9800998ecf8427e',
        'device-info': 'eyJzY3JlZW5fcmVzb2x1dGlvbiI6IjE5MjAsMTA4MCIsImF2YWlsYWJsZV9zY3JlZW5fcmVzb2x1dGlvbiI6IjE5MjAsMTA0MCIsInN5c3RlbV92ZXJzaW9uIjoiV2luZG93cyAxMCIsImJyYW5kX21vZGVsIjoidW5rbm93biIsInN5c3RlbV9sYW5nIjoiZW4tR0IiLCJ0aW1lem9uZSI6IkdNVCs4IiwidGltZXpvbmVPZmZzZXQiOi00ODAsInVzZXJfYWdlbnQiOiJNb3ppbGxhLzUuMCAoV2luZG93cyBOVCAxMC4wOyBXaW42NDsgeDY0KSBBcHBsZVdlYktpdC81MzcuMzYgKEtIVE1MLCBsaWtlIEdlY2tvKSBDaHJvbWUvMTEyLjAuMC4wIFNhZmFyaS81MzcuMzYiLCJsaXN0X3BsdWdpbiI6IlBERiBWaWV3ZXIsQ2hyb21lIFBERiBWaWV3ZXIsQ2hyb21pdW0gUERGIFZpZXdlcixNaWNyb3NvZnQgRWRnZSBQREYgVmlld2VyLFdlYktpdCBidWlsdC1pbiBQREYiLCJjYW52YXNfY29kZSI6IjY2Y2QxZjQ2Iiwid2ViZ2xfdmVuZG9yIjoiR29vZ2xlIEluYy4gKE5WSURJQSkiLCJ3ZWJnbF9yZW5kZXJlciI6IkFOR0xFIChOVklESUEsIE5WSURJQSBHZUZvcmNlIEdUWCAxMDUwIFRpIERpcmVjdDNEMTEgdnNfNV8wIHBzXzVfMCwgRDNEMTEpIiwiYXVkaW8iOiIxMjQuMDQzNDc1Mjc1MTYwNzQiLCJwbGF0Zm9ybSI6IldpbjMyIiwid2ViX3RpbWV6b25lIjoiQXNpYS9TaGFuZ2hhaSIsImRldmljZV9uYW1lIjoiQ2hyb21lIFYxMTIuMC4wLjAgKFdpbmRvd3MpIiwiZmluZ2VycHJpbnQiOiI4MGU0ODljNGZkMWQ0YTdhNGQ5NzhlOTYzNGEwMzYzOSIsImRldmljZV9pZCI6IiIsInJlbGF0ZWRfZGV2aWNlX2lkcyI6IiJ9',
        'fvideo-id': '332b3ed89496af11af79cae3b3ad780ef0735558',
        'lang': 'en',
        # 'origin': 'https://www.binance.com',
        # 'referer': 'https://www.binance.com/en/futures/trading-rules/perpetual/leverage-margin',
        'sec-ch-ua': '"Chromium";v="112", "Google Chrome";v="112", "Not:A-Brand";v="99"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': 'Windows',
        'sec-fetch-dest': 'empty',
        'sec-fetch-mode': 'cors',
        'sec-fetch-site': 'same-origin',
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36',
        'x-trace-id': 'a2145b50-9a43-47e3-bd23-7507606f8072',
        'x-ui-request-trace': 'a2145b50-9a43-47e3-bd23-7507606f8072'}

    # timestamp_ = int(time.time() * 1000)
    # datetime_ = timestamp_to_str(timestamp_ / 1000, fmt='%Y-%m-%d %H:%M:%S', tz_str='+0000')
    # date_ = timestamp_to_str(timestamp_ / 1000, fmt='%Y-%m-%d', tz_str='+0000')

    url1 = f'https://www.binance.com/bapi/futures/v1/friendly/future/common/brackets'  # U本位合约杠杆与保证金比例
    url2 = f'https://www.binance.com/bapi/futures/v1/friendly/delivery/common/brackets'  # 币本位合约杠杆与保证金比例
    post_json = {}

    all_df_list = []
    for url in [url1, url2]:
        res = requests.post(url, headers=header, cookies=cookies, json=post_json, timeout=60)
        res = json.loads(res.content)['data']['brackets']

        res_list = []
        for i in res:
            symbol_info = {'symbol': i['symbol'], 'updateTime': i['updateTime'], 'notionalLimit': i['notionalLimit']}
            for each_level_data in i['riskBrackets']:
                each_level_data.update(symbol_info)
                res_list.append(each_level_data)

        df = pd.DataFrame(res_list)
        df['updateTime'].fillna(0, inplace=True)
        timezone = '+0000'
        df['timestamp'] = df.apply(lambda x: int(x['updateTime'] / 1000), axis=1)
        df['date'] = pd.to_datetime(df['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        df['date'] = df['date'].dt.strftime('%Y-%m-%d')
        df['datetime'] = pd.to_datetime(df['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        df['datetime'] = df['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        df['time'] = pd.to_datetime(df['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        df['time'] = df['time'].dt.strftime('%H:%M:%S')

        all_df_list.append(df)

    all_df = pd.concat(all_df_list, ignore_index=True)
    # all_df[all_df['symbol'] == 'BTCUSDT']
    # all_df[all_df['symbol'] == 'BTCUSD_PERP']

    return all_df


def fetch_all_binance_funding_rate(symbol_list=['BTCUSDT', 'BTCUSD_PERP']):
    file_path = os.path.join(DATA_DIR, r'funding_rate\all_market_funding_rate')
    os.makedirs(file_path, exist_ok=True)

    end_date = get_today(marker='with_n_dash')
    # timezone = 'Asia/Shanghai'
    timezone = '+0000'

    if symbol_list == 'all':
        data_cm = fetch_binance_exchangeinfo(type='cm_futures', trading=False)
        data_cm = data_cm[data_cm['contractType'] == 'PERPETUAL']
        cm_symbol_list = list(data_cm['symbol'])
        data_um = fetch_binance_exchangeinfo(type='um_futures', trading=False)
        data_um = data_um[data_um['contractType'] == 'PERPETUAL']
        um_symbol_list = list(data_um['symbol'])

        symbol_list = []
        symbol_list.extend(cm_symbol_list)
        symbol_list.extend(um_symbol_list)

    def inner_func(symbol):
        if '_PERP' in symbol:
            market_type = 'cm_futures'
            file_name = os.path.join(file_path, f'{symbol}_funding_rate')
        else:
            market_type = 'um_futures'
            file_name = os.path.join(file_path, f'{symbol}_funding_rate')

        if os.path.exists(f'{file_name}.xlsx'):
            history_funding_rate = pd.read_excel(f'{file_name}.xlsx', index_col='datetime')
            start_date = history_funding_rate.index[-1]
            if start_date[:10] == end_date:
                print(f'{symbol}资金费率数据已经最新')
                all_data = history_funding_rate['funding_rate']
                all_data.name = symbol
                if market_type == 'cm_futures':
                    cm_data_df = all_data
                else:
                    cm_data_df = None
                if market_type == 'um_futures':
                    um_data_df = all_data
                else:
                    um_data_df = None
                return all_data, cm_data_df, um_data_df
        else:
            start_date = '2019-01-01 00:00:00'
            history_funding_rate = pd.DataFrame()

        start = time.mktime(datetime.datetime(int(start_date[:4]), int(start_date[5:7]), int(start_date[8:10]), 0, 0, 0, tzinfo=tzutc()).timetuple())
        end = time.mktime(datetime.datetime(int(end_date[:4]), int(end_date[5:7]), int(end_date[-2:]), 23, 59, 59, tzinfo=tzutc()).timetuple())

        print(f'开始获取{symbol}资金费率数据')
        data = fetch_binance_funding_rate(symbol, market_type, start, end, '8h')

        data['timestamp'] = data.apply(lambda x: int(x['timestamp'] / 1000), axis=1)
        data['date'] = pd.to_datetime(data['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        data['date'] = data['date'].dt.strftime('%Y-%m-%d')
        data['datetime'] = pd.to_datetime(data['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        data['datetime'] = data['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        data['time'] = pd.to_datetime(data['timestamp'], unit='s').dt.tz_localize('UTC').dt.tz_convert(timezone)
        data['time'] = data['time'].dt.strftime('%H:%M:%S')
        data.set_index('datetime', inplace=True)
        data.sort_index(inplace=True)

        all_data = pd.concat([history_funding_rate, data])
        all_data = all_data[~all_data.index.duplicated(keep='first')]

        file_name = os.path.join(file_path, f'{symbol}_funding_rate')
        all_data.to_excel(f'{file_name}.xlsx')

        all_data = all_data['funding_rate']
        all_data.name = symbol
        if market_type == 'cm_futures':
            cm_data_df = all_data
        else:
            cm_data_df = None
        if market_type == 'um_futures':
            um_data_df = all_data
        else:
            um_data_df = None
        time.sleep(1)

        return all_data, cm_data_df, um_data_df

    result = Parallel(n_jobs=30, verbose=10)(delayed(inner_func)(param) for param in symbol_list)
    all_data_df_list, all_cm_data_df_list, all_um_data_df_list = zip(*result)
    all_data_df_list = [i for i in all_data_df_list if i is not None]
    all_cm_data_df_list = [i for i in all_cm_data_df_list if i is not None]
    all_um_data_df_list = [i for i in all_um_data_df_list if i is not None]
    all_funding_rate_df = pd.concat(all_data_df_list, axis=1)
    all_cm_funding_rate_df = pd.concat(all_cm_data_df_list, axis=1)
    all_um_funding_rate_df = pd.concat(all_um_data_df_list, axis=1)
    all_funding_rate_df.sort_index(inplace=True)
    all_cm_funding_rate_df.sort_index(inplace=True)
    all_um_funding_rate_df.sort_index(inplace=True)

    file_name = os.path.join(file_path, f'all_market_funding_rate')
    all_funding_rate_df.to_excel(f'{file_name}.xlsx')
    file_name = os.path.join(file_path, f'all_cm_funding_rate')
    all_cm_funding_rate_df.to_excel(f'{file_name}.xlsx')
    file_name = os.path.join(file_path, f'all_um_funding_rate')
    all_um_funding_rate_df.to_excel(f'{file_name}.xlsx')

    # file_name = os.path.join(file_path, f'all_market_funding_rate')
    # all_funding_rate_df = pd.read_excel(f'{file_name}.xlsx', index_col='datetime')
    # file_name = os.path.join(file_path, f'all_cm_funding_rate')
    # all_cm_funding_rate_df = pd.read_excel(f'{file_name}.xlsx', index_col='datetime')
    # file_name = os.path.join(file_path, f'all_um_funding_rate')
    # all_um_funding_rate_df = pd.read_excel(f'{file_name}.xlsx', index_col='datetime')

    temp_index = [i for i in all_funding_rate_df.index if i[11:13] in ['00', '08', '16']]  # 只保留三个时间点的
    all_funding_rate_df = all_funding_rate_df.loc[temp_index, :]
    temp_index = [i for i in all_cm_funding_rate_df.index if i[11:13] in ['00', '08', '16']]  # 只保留三个时间点的
    all_cm_funding_rate_df = all_cm_funding_rate_df.loc[temp_index, :]
    temp_index = [i for i in all_um_funding_rate_df.index if i[11:13] in ['00', '08', '16']]  # 只保留三个时间点的
    all_um_funding_rate_df = all_um_funding_rate_df.loc[temp_index, :]

    temp_file_path = os.path.join(DATA_DIR, f'sth_momentum')
    temp_file_name = os.path.join(temp_file_path, f'glassnode_btcusdt_hourly_ohlcv')
    hourly_ohlcv = pd.read_excel(f'{temp_file_name}.xlsx', index_col='end_date')

    for index, df in enumerate([all_funding_rate_df, all_cm_funding_rate_df, all_um_funding_rate_df]):
        df = df.astype(float)
        analysis_df = pd.DataFrame()
        analysis_df['total_num'] = (~df.isnull()).sum(axis=1)
        analysis_df['neutral_num'] = (df == 0.0001).sum(axis=1)
        analysis_df['positive_num'] = (df > 0.0001).sum(axis=1)
        analysis_df['negative_num'] = (df < 0.0001).sum(axis=1)
        analysis_df['extreme_positive_num'] = (df > 0.005).sum(axis=1)
        analysis_df['extreme_negative_num'] = (df < -0.005).sum(axis=1)
        analysis_df['neutral_ratio'] = analysis_df['neutral_num'] / analysis_df['total_num']
        analysis_df['positive_ratio'] = analysis_df['positive_num'] / analysis_df['total_num']
        analysis_df['negative_ratio'] = analysis_df['negative_num'] / analysis_df['total_num']
        analysis_df['extreme_positive_ratio'] = analysis_df['extreme_positive_num'] / analysis_df['total_num']
        analysis_df['extreme_negative_ratio'] = analysis_df['extreme_negative_num'] / analysis_df['total_num']
        analysis_df['neutral_ratio_ma7'] = analysis_df['neutral_ratio'].rolling(7).mean()
        analysis_df['positive_ratio_ma7'] = analysis_df['positive_ratio'].rolling(7).mean()
        analysis_df['negative_ratio_ma7'] = analysis_df['negative_ratio'].rolling(7).mean()

        all_analysis_df = analysis_df.merge(hourly_ohlcv, how='left', left_index=True, right_index=True)

        if index == 0:
            file_name = os.path.join(file_path, f'all_market_funding_rate_analysis')
        elif index == 1:
            file_name = os.path.join(file_path, f'all_cm_funding_rate_analysis')
        elif index == 2:
            file_name = os.path.join(file_path, f'all_um_funding_rate_analysis')
        else:
            raise NotImplementedError
        all_analysis_df.to_excel(f'{file_name}.xlsx')

    # BTC_USDT_funding_rate = all_data_df_list[0]
    # BTC_USDT_funding_rate.rename(columns={'funding_rate': 'funding_rate_BTCUSDT'}, inplace=True)
    # BTC_USD_funding_rate = all_data_df_list[1]
    # BTC_USD_funding_rate.rename(columns={'funding_rate': 'funding_rate_BTCUSD'}, inplace=True)
    # all_df = pd.concat([BTC_USDT_funding_rate['funding_rate_BTCUSDT'], BTC_USD_funding_rate['funding_rate_BTCUSD']], axis=1)
    # all_df['delta_funding_rate'] = all_df['funding_rate_BTCUSDT'] - all_df['funding_rate_BTCUSD']
    # file_path = os.path.join(DATA_DIR, f'funding_rate')
    # file_name = os.path.join(file_path, f'Binance_funding_rate_analysis')
    # all_analysis_df.to_excel(f'{file_name}.xlsx')


if __name__ == '__main__':
    # start = time.mktime(datetime.datetime(2022, 1, 1, 0, 0, 0, tzinfo=tzutc()).timetuple())
    # end = time.mktime(datetime.datetime(2022, 9, 6, 23, 0, 0, tzinfo=tzutc()).timetuple())

    today = get_today(marker="with_n_dash")
    file_path = os.path.join(DATA_DIR)
    # 测试fetch_binance_exchangeinfo， fetch_binance_kline
    for type in ['spot', 'um_futures', 'cm_futures']:
        data_spot = fetch_binance_exchangeinfo(type=type, trading=False)
        file_name = os.path.join(file_path, f'binance_{type}_exchange_info_{today}')
        data_spot.to_excel(f'{file_name}.xlsx')
    # data_cm = fetch_binance_exchangeinfo(type=
    # 'cm_futures', trading=False)
    # data_um = fetch_binance_exchangeinfo(type='um_futures', trading=False)
    # data = fetch_binance_kline("BTCUSDT", start, end, '1h')
    # data.sort_values(by='datetime', inplace=True)
    # file_path = os.path.join(DATA_DIR)
    # file_name = os.path.join(file_path, f'binance_ohlcv')
    # data.to_excel(f'{file_name}.xlsx')

    # # 测试fetch_binance_margin_interest_rate
    # for asset in ['USDT', 'BTC', 'ETH']:
    #     frame = fetch_binance_margin_interest_rate(asset=asset, start_time=start, end_time=end)
    #     frame['start_time'] = frame.apply(lambda x: int(x['timestamp'] / 1000), axis=1)
    #     frame['date'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('Asia/Shanghai')
    #     frame['date'] = frame['date'].dt.strftime('%Y-%m-%d')
    #     frame['datetime'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('Asia/Shanghai')
    #     frame['datetime'] = frame['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
    #     frame.set_index('date', inplace=True)
    #     file_path = os.path.join(DATA_DIR, f'margin_interest_rate')
    #     os.makedirs(file_path, exist_ok=True)
    #     file_name = os.path.join(file_path, f'binance_{asset}_margin_interest_rate')
    #     frame.to_excel(f'{file_name}.xlsx')

    # fetch_binance_usdtcny_premium()

    fetch_binance_perpetual_leverage_margin()

    fetch_all_binance_funding_rate(symbol_list='all')
    # fetch_all_binance_funding_rate(symbol_list=['BTCUSDT', 'BTCUSD_PERP'])